﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

/*
 * 
 * Dateien für Unittests
 * Walkthrough: Using a Configuration File to Define a Data Source
 * http://msdn.microsoft.com/en-us/library/ms243192.aspx
 * 
 */
namespace Markus
{
    public static class OleDb
    {
        /// <summary>
        /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// /// MIST: Open ist auch Open wenn die Datei garnicht existiert.
        /// </summary>
        /// <param name="filename"></param>
        /// <returns>connection is not open</returns>
        public static OleDbConnection fileName2OleDbConnection(string fileName)
        {
            OleDbConnection con;
            string connectionString;

            if (fileName.EndsWith(".xlsx"))
                //read a 2007 file
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
            else
                //read a 97-2003 file
              connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";

            con = new OleDbConnection(connectionString);
            return con;
        }



        /// <summary>
        /// 
        /// Excel worksheets with spaces are ' ... $'
        /// http://stackoverflow.com/questions/1306649/obtaining-first-excel-sheet-name-with-oledbconnection
        /// 
        /// Instead of OleDB
        /// NPOI
        /// 
        /// good hint
        /// http://stackoverflow.com/questions/10654433/how-to-get-list-of-only-excel-worksheet-names-in-excel-using-oledb-filter-out-n
        /// 
        /// Strange 
        /// http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/f0762ebc-37fe-4624-915f-53e2df7193dc/
        /// 
        /// </summary>
        /// <param name="con">must be open, remains open</param>
        public static List<String> listOfTables(OleDbConnection con)
        {
            List<String> workSheetNames = new List<String>();
            if (con == null)
                return workSheetNames;

            //get all sheets via the schema
            /*
             * 
             * ordered. 
             * 
             * 
             * 
             * second parameter
             * restrictions
             * http://support.microsoft.com/kb/309681/DE
             * 
             * new object[] { null, null, null, "TABLE" }
             * no influence
             * 
             * 
             * 
             */

            System.Data.DataTable schemaDataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            foreach (DataRow row in schemaDataSet.Rows)
            {
                String workSheetName = row["TABLE_NAME"].ToString();
                bool isExcel = con.DataSource.EndsWith(".xls") || con.DataSource.EndsWith(".xlsx");

                /*
                 * Excel sheet ends with an $
                 * Internal(?) don't
                 * Documentation: TODO
                 * 
                 * 
                 */
                if (isExcel)
                {
                    //insert the sheet's name only if it ends on $ or ' xxxx $'
                    if (workSheetName.EndsWith("$"))
                    {
                        workSheetName = workSheetName.TrimEnd(new[] { '$' });  //remove the $ sign at the end
                        workSheetNames.Add(workSheetName);
                    } else if (workSheetName.StartsWith("'") && workSheetName.EndsWith("$'"))
                    {
                        workSheetName = workSheetName.TrimEnd(new[] { '\'' });  //remove the ' sign at the end
                        workSheetName = workSheetName.TrimEnd(new[] { '$' });  //remove the $ sign at the end
                        workSheetName = workSheetName.TrimStart(new[] { '\'' });  //remove the ' sign at the start
                        workSheetNames.Add(workSheetName);
                    } 
                }
                else
                {
                    workSheetNames.Add(workSheetName);
                }
            }

            bool IsThisNecessaryAndWhy = false;
            if (IsThisNecessaryAndWhy)
            {
                if (schemaDataSet != null)
                    schemaDataSet.Dispose();
            }
            return workSheetNames;
        }
    }
}
